package com.lwj.utils.excel;

import java.awt.*;
import java.awt.Color;
import java.awt.Font;
import java.awt.font.FontRenderContext;
import java.awt.geom.Rectangle2D;
import java.awt.image.BufferedImage;
import java.io.*;
import java.lang.reflect.Field;
import java.net.URLEncoder;
import java.nio.charset.StandardCharsets;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Properties;

import com.lwj.utils.XmlProperty;
import org.apache.poi.openxml4j.opc.PackagePartName;
import org.apache.poi.openxml4j.opc.PackageRelationship;
import org.apache.poi.openxml4j.opc.TargetMode;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.streaming.SXSSFCell;
import org.apache.poi.xssf.streaming.SXSSFRow;
import org.apache.poi.xssf.streaming.SXSSFSheet;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.*;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.util.StringUtils;

import javax.imageio.ImageIO;
import javax.servlet.http.HttpServletResponse;

/**
 * <p>
 * <b>excel编辑工具类</b>, 支持多个sheet, 且可以在各个sheet之间切换
 * <p>
 *
 * <p>
 * 注意: <br>
 * (1) 每个sheet最大行数是 <b>1048576行</b> <br>
 * (2) <b>excel</b>文件的后缀名是 <b>xlsx</b>
 * </p>
 *
 * <p>
 * 测试: <br>
 * (1) 单个 sheet, 每行 43 个字符串字段，1048576行，10分钟52秒成功导入, 总行数: <b>1048576</b> 行 <br>
 * (2) 两个 sheet, 每个 sheet 每行 43 个字符串字段，每个 sheet 都有 1048576行，27分钟43秒成功导入, 总行数:
 * <b>(1048576 * 2) </b>行
 * </p>
 *
 * @author liliwenjian
 *
 */
public class PoiXlsWriter {
	private static final Logger logger = LoggerFactory.getLogger(PoiXlsWriter.class);
	private OutputStream out;
	private SXSSFWorkbook workbook;

	private SXSSFSheet currentSheet;
	private int rowNum;
	private int sheetNum;

	public PoiXlsWriter() {
		workbook = new SXSSFWorkbook();
	}

	public PoiXlsWriter( String sheetName) {
		workbook = new SXSSFWorkbook();
		addSheet(sheetName);
	}
	public  OutputStream initOutputStream(String fileName)  {
		try {
			File file=new File(fileName);
			if(!file.exists()){
				file.createNewFile();
			}
			out=new FileOutputStream(file);
			return out;
		} catch (Exception e) {
			logger.error("导出excel报错",e);
			throw new RuntimeException("导出excel报错");
		}
	}
	public  void initOutputStream(HttpServletResponse response,String fileName)  {
        try {
			response.setContentType("application/force-download");
			fileName= replaceSheetName(fileName);
			fileName = URLEncoder.encode(fileName + ".xlsx", StandardCharsets.UTF_8.toString());
			fileName = fileName.replaceAll("\\+", "%20");
			response.setHeader("Content-Disposition", "attachment;filename=" + fileName.replaceAll("%2B", "+"));
			this.out=response.getOutputStream();
		}catch (Exception e){
            logger.error("导出excel报错",e);
			throw new RuntimeException("导出excel报错");
		}
	}
	public  String replaceSheetName(String sheetName) {
		if (StringUtils.hasText(sheetName)) {
			return sheetName.replaceAll("[/:*\"?\\\\<>|]", "-");
		}else {
			return "";
		}
	}
	public void addSheet(String sheetName) {
		currentSheet= workbook.createSheet(replaceSheetName(sheetName));
		rowNum=0;
		sheetNum++;
	}
	/**
	 * 向excel文件里追加一行
	 *
	 * @param record
	 */
	public void addRow(String[] record) {
		SXSSFRow row = currentSheet.createRow(rowNum);
		for (int i = 0; i < record.length; i++) {
			SXSSFCell cell = row.createCell((short) i);
			String value = record[i];
			cell.setCellValue(new XSSFRichTextString(value));
		}
		rowNum++;
	}
	/**
	 * 关闭文件
	 */
	public void close() {
		try {
			if (out != null && workbook != null) {
				workbook.write(out);
				out.flush();
				out.close();
			}
		} catch (IOException e) {
			e.printStackTrace();
		}
	}
	/**
	 * @date 2020/10/10 19:21
	 * @Description 生成图片水印
	 */
	public  ByteArrayOutputStream addWaterMark(String content) throws IOException {
		int width = 200;
		int height = 150;
		BufferedImage image = new BufferedImage(width, height, BufferedImage.TYPE_INT_RGB);// 获取bufferedImage对象
		String fontType = "微软雅黑";
		int fontStyle = Font.BOLD;
		int fontSize = 20;
		Font font = new Font(fontType, fontStyle, fontSize);
		Graphics2D g2d = image.createGraphics(); // 获取Graphics2d对象
		image = g2d.getDeviceConfiguration().createCompatibleImage(width, height, Transparency.TRANSLUCENT);
		g2d.dispose();
		g2d = image.createGraphics();
		g2d.setColor(new Color(0, 0, 0, 20)); //设置字体颜色和透明度，最后一个参数为透明度
		g2d.setStroke(new BasicStroke(1)); // 设置字体
		g2d.setFont(font); // 设置字体类型  加粗 大小
		g2d.rotate(-0.5, (double) image.getWidth() / 2, (double) image.getHeight() / 2);//设置倾斜度
		FontRenderContext context = g2d.getFontRenderContext();
		Rectangle2D bounds = font.getStringBounds(content, context);
		double x = (width - bounds.getWidth()) / 2;
		double y = (height - bounds.getHeight()) / 2;
		double ascent = -bounds.getY();
		double baseY = y + ascent;
		// 写入水印文字原定高度过小，所以累计写水印，增加高度
		g2d.drawString(content, (int) x, (int) baseY);
		// 设置透明度
		g2d.setComposite(AlphaComposite.getInstance(AlphaComposite.SRC_OVER));
		// 释放对象
		g2d.dispose();
		ByteArrayOutputStream os = new ByteArrayOutputStream();
		ImageIO.write(image, "png", os);
		return os;
	}
	/**
	 * 给 Excel 添加水印
	 *
	 * @param waterMarkText 水印文字内容
	 */
	public  void insertWaterMarkTextToXlsx( String waterMarkText) throws Exception {
		ByteArrayOutputStream imageOs = addWaterMark(waterMarkText);
		int pictureIdx = workbook.addPicture(imageOs.toByteArray(), XSSFWorkbook.PICTURE_TYPE_PNG);
		XSSFPictureData pictureData = (XSSFPictureData) workbook.getAllPictures().get(pictureIdx);
		for (int i = 0; i < workbook.getNumberOfSheets(); i++) {//获取每个Sheet表
			SXSSFSheet sheet = workbook.getSheetAt(i);
			//这里由于 SXSSFSheet 没有 getCTWorksheet() 方法，通过反射取出 _sh 属性
			Field sh = SXSSFSheet.class.getDeclaredField("_sh");
			sh.setAccessible(true);//设置访问权限
			XSSFSheet shReflect =(XSSFSheet)sh.get(sheet);
			sh.setAccessible(false);
			PackagePartName ppn = pictureData.getPackagePart().getPartName();
			String relType = XSSFRelation.IMAGES.getRelation();
			PackageRelationship pr = shReflect.getPackagePart().addRelationship(ppn, TargetMode.INTERNAL, relType, null);
			shReflect.getCTWorksheet().addNewPicture().setId(pr.getId());
		}
	}
	public CellStyle createCellStyle(Boolean wrapText, HorizontalAlignment horizontalAlignment, VerticalAlignment verticalAlignment, String fontName, int fontSize  ){
		CellStyle cellStyle = workbook.createCellStyle();
		//换行符是否生效
		cellStyle.setWrapText(wrapText);
		// 居中:HorizontalAlignment.CENTER
		cellStyle.setAlignment(horizontalAlignment);
		cellStyle.setVerticalAlignment(verticalAlignment);
		org.apache.poi.ss.usermodel.Font font = workbook.createFont();
		font.setFontName(fontName);
		//设置字体大小
		font.setFontHeightInPoints(((short)fontSize));
		cellStyle.setFont(font);
		//设置文本边框
		cellStyle.setBottomBorderColor(IndexedColors.BLACK.getIndex());
		//左边框
		cellStyle.setBorderLeft(BorderStyle.THIN);
		cellStyle.setLeftBorderColor(IndexedColors.BLACK.getIndex());
		//上边框
		cellStyle.setBorderTop(BorderStyle.THIN);
		cellStyle.setRightBorderColor(IndexedColors.BLACK.getIndex());
		//右边框
		cellStyle.setBorderRight(BorderStyle.THIN);
		cellStyle.setTopBorderColor(IndexedColors.BLACK.getIndex());
		cellStyle.setBorderBottom(BorderStyle.THIN);
		return cellStyle;
	}
	public CellStyle headCellStyle(){
		CellStyle cellStyle = workbook.createCellStyle();
		cellStyle.setBorderBottom(BorderStyle.THIN);
		cellStyle.setBorderLeft(BorderStyle.THIN);
		cellStyle.setBorderTop(BorderStyle.THIN);
		cellStyle.setBorderRight(BorderStyle.THIN);
		//设置单元格颜色（颜色对应枚举会放在下面）
		cellStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
		//全部填充 （填充枚举对应的样式也会放在下面）
		cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
		//换行符是否生效
		cellStyle.setWrapText(true);
		// 居中:HorizontalAlignment.CENTER
		cellStyle.setAlignment(HorizontalAlignment.CENTER);
		cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
		org.apache.poi.ss.usermodel.Font font = workbook.createFont();
		font.setFontName("黑体");
		font.setBold(true);
		//设置字体大小
		font.setFontHeightInPoints(((short) 10));
		cellStyle.setFont(font);
		return cellStyle;
	}
	public CellStyle contentCellStyle(){
		CellStyle cellStyle = workbook.createCellStyle();
		//换行符是否生效
		cellStyle.setWrapText(true);
		// 居中:HorizontalAlignment.CENTER
		cellStyle.setAlignment(HorizontalAlignment.CENTER);
		cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
		org.apache.poi.ss.usermodel.Font font = workbook.createFont();
		font.setFontName("宋体");
		//设置字体大小
		font.setFontHeightInPoints(((short) 11));
		cellStyle.setFont(font);
		return cellStyle;
	}
	public static void main(String[] args) {
		System.out.println(new Date());
		PoiXlsWriter poiXlsWriter = new PoiXlsWriter();
		try (OutputStream outputStream = poiXlsWriter.initOutputStream("D:\\test.xlsx")){
			poiXlsWriter.initOutputStream("D:\\test.xlsx");
			poiXlsWriter.addSheet("测试");
			for (int i = 0; i < 100; i++) {
				poiXlsWriter.addRow(new String[]{"21222222","21222222","21222222","21222222","21222222","21222222","21222222","21222222","21222222","21222222","21222222"});
			}
			poiXlsWriter.insertWaterMarkTextToXlsx("123456");
			poiXlsWriter.close();
			System.out.println(new Date());
		}catch (Exception e){
            throw new RuntimeException("导出excel报错",e);
		}

	}
}
